const mysql = require('mysql2/promise');

// 使用 .env 中的数据库配置
const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    connectionLimit: 20,
    waitForConnections: true,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 10000,
    idleTimeout: 5000, // 5秒空闲后自动断开连接
    timezone: '+08:00',
    dateStrings: true,
    typeCast: function (field, next) {
        if (field.type === 'DATETIME' || field.type === 'TIMESTAMP') {
            return field.string();
        }
        return next();
    }
});

// 在文件顶部添加颜色常量
// 在颜色常量后添加时间格式化函数
const colors = {
    reset: '\x1b[0m',
    green: '\x1b[32m',
    yellow: '\x1b[33m',
    cyan: '\x1b[36m',
    gray: '\x1b[90m' // 新增灰色用于时间显示
};

// 新增时间格式化函数
function getBeijingTime() {
    return new Date().toLocaleString('zh-CN', {
        timeZone: 'Asia/Shanghai',
        year: 'numeric',
        month: '2-digit',
        day: '2-digit',
        hour: '2-digit',
        minute: '2-digit',
        second: '2-digit',
        fractionalSecondDigits: 3
    }).replace(/(\d+)\/(\d+)\/(\d+),/, '$3-$1-$2 ').replace('上午', ' ').replace('下午', ' ');
}

// 修改所有日志输出（示例修改 startTransaction）
// 在 pool 定义后添加状态追踪变量
let lastUsedCount = 0;

// 修改 startTransaction 方法
const startTransaction = async () => {
    const connection = await pool.getConnection();
    const currentUsed = pool.pool._allConnections.length - pool.pool._freeConnections.length;

    // 仅当使用数变化时记录日志
    if (currentUsed !== lastUsedCount) {
        const time = getBeijingTime();
        console.log(`${colors.gray}[${time}]${colors.reset} ${colors.green}▲ MySQL连接获取 ${colors.reset}（空闲/使用中/总数: ${colors.cyan}${pool.pool._freeConnections.length}${colors.reset}/${colors.yellow}${currentUsed}${colors.reset}/${colors.cyan}${pool.pool.config.connectionLimit}${colors.reset}）`);
        lastUsedCount = currentUsed;
    }

    try {
        await connection.beginTransaction();
        return connection;
    } catch (err) {
        console.log(`${colors.yellow}▼ MySQL异常释放 ${colors.reset}`);
        connection.release();
        throw err;
    }
};

// 修改 commitTransaction 方法
const commitTransaction = async (connection) => {
    try {
        if (connection._closed) return;
        await connection.commit();
    } finally {
        if (!connection._closed) {
            const currentUsed = pool.pool._allConnections.length - pool.pool._freeConnections.length - 1;

            if (currentUsed !== lastUsedCount) {
                const time = getBeijingTime();
                console.log(`${colors.gray}[${time}]${colors.reset} ${colors.yellow}▼ MySQL连接释放 ${colors.reset}（空闲/使用中/总数: ${colors.cyan}${pool.pool._freeConnections.length + 1}${colors.reset}/${colors.yellow}${currentUsed}${colors.reset}/${colors.cyan}${pool.pool.config.connectionLimit}${colors.reset}）`);
                lastUsedCount = currentUsed;
            }

            connection.release();
            connection._closed = true;
        }
    }
};

// 事务回滚后释放连接
const rollbackTransaction = async (connection) => {
    try {
        await connection.rollback();
    } finally {
        connection.release();  // ✅ 正确释放连接
    }
};

module.exports = {
    pool,
    startTransaction,
    commitTransaction,
    rollbackTransaction
};